package org.abettor.nookinfo.persist;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.abettor.nookinfo.AddressConstant;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * 本地图片持久化工具类
 * @author shawn
 *
 */
public class PhotoHelper
{
	private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private Context context;

	public PhotoHelper(Context context)
	{
		this.context = context;
	}

	/**
	 * 保存或更新已有图片分类信息
	 */
	public synchronized void saveOrUpdateCategory(PhotoCategory category)
	{
		PhotoCategory pc = getCategory(category.id);

		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getWritableDatabase();
		if(pc == null)
		{
			ContentValues cv = new ContentValues();
			cv.put("n_id", category.id);
			cv.put("c_name", category.name);
			cv.put("c_description", category.description);
			cv.put("c_update", DATE_FORMAT.format(category.update));
			db.insert("tbl_photo_category", null, cv);
		}
		else
		{
			ContentValues cv = new ContentValues();
			cv.put("c_name", category.name);
			cv.put("c_description", category.description);
			cv.put("c_update", DATE_FORMAT.format(category.update));
			db.update("tbl_photo_category", cv, "n_id = ?", new String[]{category.id + ""});
		}

		db.close();
		doh.close();
	}

	/**
	 * 获取本地图片分类列表
	 */
	public synchronized List<PhotoCategory> getCategoryList()
	{
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_photo_category", null, null, null, null, null, "c_update desc");
		if(cursor != null)
		{
			List<PhotoCategory> list = new ArrayList<PhotoCategory>();
			cursor.moveToFirst();
			while(! cursor.isAfterLast())
			{
				PhotoCategory pc = new PhotoCategory();
				pc.id = cursor.getInt(0);
				pc.name = cursor.getString(1);
				pc.description = cursor.getString(2);
				try
				{
					pc.update = DATE_FORMAT.parse(cursor.getString(3));
				}
				catch (ParseException e)
				{
					Log.d(this.getClass().getName(), "", e);
				}

				list.add(pc);
				cursor.moveToNext();
			}

			cursor.close();
			db.close();
			doh.close();
			return list;
		}

		db.close();
		doh.close();
		return null;
	}

	/**
	 * 获取图片分类信息
	 */
	public synchronized PhotoCategory getCategory(int id)
	{
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_photo_category", null, "n_id = ?", new String[]{id + ""}, null, null, null, "0, 1");
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}

		cursor.moveToFirst();
		if(! cursor.isAfterLast())
		{
			PhotoCategory pc = new PhotoCategory();
			pc.id = cursor.getInt(0);
			pc.name = cursor.getString(1);
			pc.description = cursor.getString(2);
			try
			{
				pc.update = DATE_FORMAT.parse(cursor.getString(3));
			}
			catch (ParseException e)
			{
				Log.d(this.getClass().getName(), "", e);
				cursor.close();
				db.close();
				doh.close();
				return null;
			}

			cursor.close();
			db.close();
			doh.close();
			return pc;
		}

		cursor.close();
		db.close();
		doh.close();
		return null;
	}

	/**
	 * 删除图片分类信息
	 */
	public synchronized void removeCategory(int id)
	{
		PhotoCategory pc = getCategory(id);
		if(pc != null)
		{
			//	获取图片记录信息并删除图片文件
			DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
			SQLiteDatabase db = doh.getWritableDatabase();
			Cursor cursor = db.query("tbl_photo", null, "n_category = ?", new String[]{id + ""}, null, null, null);
			if(cursor != null)
			{
				cursor.moveToFirst();
				while(! cursor.isAfterLast())
				{
					int photoId = cursor.getInt(2);
					String path = AddressConstant.PHOTO_ORIGINAL_IMAGE_LOCAL.replace("ID", photoId + "");
					File file = new File(path);
					if(file.exists())
					{
						file.delete();
					}

					cursor.moveToNext();
				}

				cursor.close();
			}

			//	删除图片记录
			db.delete("tbl_photo", "n_category = ?", new String[]{id + ""});

			//	删除分类记录
			db.delete("tbl_photo_category", "n_id = ?", new String[]{id + ""});

			db.close();
			doh.close();
		}
	}

	/**
	 * 获取本地图片页数
	 * @param category
	 * @param pageSize
	 */
	public synchronized int getPhotoPageCount(int category, int pageSize)
	{
		String sql = "SELECT COUNT(*) FROM tbl_photo WHERE n_category = ?";
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[]{category + ""});
		if(cursor == null)
		{
			db.close();
			doh.close();
			return 0;
		}

		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return 0;
		}

		int total = cursor.getInt(0);
		int totalPage = total / pageSize;
		if(total % pageSize > 0)
		{
			totalPage ++;
		}

		cursor.close();
		db.close();
		doh.close();
		return totalPage;
	}

	public synchronized List<Integer> getPhotoList(int category, int pageSize, int pageNo)
	{
		int startIdx = (pageNo - 1) * pageSize;
		String limit = startIdx + ", " + pageSize;

		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_photo", new String[]{"n_photo", "n_order"}, "n_category = ?", new String[]{category + ""}, null, null, "n_order desc", limit);
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}

		List<Integer> list = new ArrayList<Integer>();
		cursor.moveToFirst();
		while(! cursor.isAfterLast())
		{
			int photo = cursor.getInt(0);
			list.add(photo);
			cursor.moveToNext();
		}

		cursor.close();
		db.close();
		doh.close();
		return list;
	}

	/**
	 * 保存图片记录
	 * @param photo
	 * @param category
	 */
	public synchronized void savePhoto(int photo, int category)
	{
		//	先检查有没有存在的记录
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getWritableDatabase();
		String sql = "SELECT COUNT(*) FROM tbl_photo WHERE n_photo = ?";
		Cursor cursor = db.rawQuery(sql, new String[]{photo + ""});
		if(cursor != null)
		{
			cursor.moveToFirst();
			if(! cursor.isAfterLast())
			{
				int count = cursor.getInt(0);
				if(count > 0)
				{
					//	有重复，直接跳过
					cursor.close();
					db.close();
					doh.close();
					return;
				}
			}

			cursor.close();
		}

		//	没有重复，插入
		ContentValues cv = new ContentValues();
		cv.put("n_photo", photo);
		cv.put("n_category", category);
		db.insert("tbl_photo", null, cv);
		db.close();
		doh.close();
	}

	/**
	 * 获取上一个图片的ID
	 * @param id 参考图片的ID
	 * @return 成功返回对应图片的ID，否则返回null
	 */
	public synchronized Integer getPrevPhotoById(int id)
	{
		//	首先获取当前图片所属的类型
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_photo", new String[]{"n_category", "n_order"}, "n_photo = ?", new String[]{id + ""}, null, null, null);
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}
		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		int category = cursor.getInt(0);//	得到类别ID
		int order = cursor.getInt(1);//	得到当前排序值                   
		cursor.close();

		//	查询下一个
		cursor = db.query("tbl_photo", new String[]{"n_photo", "n_order"}, "n_order > ? and n_category = ?", new String[]{order + "", category + ""}, null, null, "n_order asc", "0, 1");
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}
		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		int photo = cursor.getInt(0);
		cursor.close();
		db.close();
		doh.close();
		return photo;
	}

	/**
	 * 获取下一个图片的ID
	 * @param id 参考图片的ID
	 * @return 成功返回对应图片的ID，否则返回null
	 */
	public synchronized Integer getNextPhotoById(int id)
	{
		//	首先获取当前图片所属的类型
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_photo", new String[]{"n_category", "n_order"}, "n_photo = ?", new String[]{id + ""}, null, null, null);
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}
		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		int category = cursor.getInt(0);//	得到类别ID
		int order = cursor.getInt(1);//	得到当前排序值                   
		cursor.close();

		//	查询下一个
		cursor = db.query("tbl_photo", new String[]{"n_photo", "n_order"}, "n_order < ? and n_category = ?", new String[]{order + "", category + ""}, null, null, "n_order desc", "0, 1");
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}
		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		int photo = cursor.getInt(0);
		cursor.close();
		db.close();
		doh.close();
		return photo;
	}

	/**
	 * 删除指定的本地记录
	 * @param photo
	 */
	public synchronized void removePhoto(int photo)
	{
		//	首先找到并删除记录
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getWritableDatabase();
		db.delete("tbl_photo", "n_photo = ?", new String[]{photo + ""});
		db.close();
		doh.close();

		//	删除文件
		String path = AddressConstant.PHOTO_ORIGINAL_IMAGE_LOCAL.replace("ID", photo + "");
		File file = new File(path);
		if(file.exists())
		{
			file.delete();
		}
	}

	/**
	 * 图片分类信息
	 * @author shawn
	 *
	 */
	public static class PhotoCategory
	{
		public int id;
		public String name;
		public String description;
		public Date update;
	}
}
